package database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * Database class will act as a Database. Controllers can read and write to
 * and from a real database by means of this class.
 * @author Dustin
 * @version 1.0
 */
public class Database {

    /**
     * The bad chatacters a data input may not contain.
     */
    public final static char[] BAD_CHARS = {'"', '\'', '-'};

    private boolean connected;
    private Connection connection;
    private Statement statement;

    public Database() {
        try {
            connect();
            query("SELECT * FROM players");
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        } catch (SQLException ex) {
            try {
                update("CREATE TABLE players(" +
                        "name VARCHAR(50), " +
                        "password VARCHAR(50)" +
                        ")"
                      );
                update("INSERT INTO players(name, password) " +
                        "VALUES " +
                        "('Dustin', 'foo')"
                      );
            } catch (SQLException ex1) {
                ex.printStackTrace();
            }
        }
    }

    /**
     * Returns if the given command contains bad characters.
     * @param command the command to check the vality of
     * @return true if the given command contains no bad characters; false otherwise
     */
    public static boolean isValidCommand(String command) {
        boolean isValid = true;
        for (int i = 0; i < BAD_CHARS.length && isValid; i++) {
            for (int j = 0; j < command.length() && isValid; j++) {
                if (BAD_CHARS[i] == command.charAt(j)) {
                    isValid = false;
                    System.out.println("Command not valid; " + command);
                }
            }
        }
        return isValid;
    }

    /**
     * Returns if a connection has been made with a database.
     * @return true if the database is connected
     */
    public boolean isConnected() {
        return connected;
    }

    /**
     * Query the connected database with the given command only if the given command is valid.
     * @param command the command to execute to the database
     * @return a java.sql.ResultSet if connected and given command is valid; null otherwise
     * @throws java.sql.SQLException if an error appears when excecuting the command
     */
    public ResultSet query(String command) throws SQLException {
        ResultSet results = null;
        if(connected && isValidCommand(command)) {
            System.out.println("Database query: " + command);
            results = statement.executeQuery(command);
        }
        return results;
    }

    /**
     * Update the connected database with the given command only if the given command is valid.
     * @param command the command to execute to the database
     * @throws java.sql.SQLException if an error appears when excecuting the command
     */
    public void update(String command) throws SQLException {
        if(connected && isValidCommand(command)) {
            System.out.println("Database update: " + command);
            statement.executeUpdate(command);
        }
    }

    /**
     * Connect to a database.
     * @param driverName the name of the drivers to load e.g.: org.apache.derby.jdbc.EmbeddedDriver
     * @param databaseName the name of the database name e.g.: derby
     * @param databaseURL the URL of the database e.g.: BirthdayReminder
     * @param username the username to connect to the database
     * @param password the password to connect to the database
     * @throws java.lang.ClassNotFoundException
     * @throws java.sql.SQLException
     */
    public void connect() throws ClassNotFoundException, SQLException {
        if(!connected) {
            Class.forName("org.apache.derby.jdbc.EmbeddedDriver");

            connection = DriverManager.getConnection("jdbc:derby:eenendertigen;create=true;");
            statement = connection.createStatement();
            connected = true;
            System.out.println("Database connected");
        }
    }

    /**
     * Disconnect from the connected database by closing the Statement and Connection.
     * @throws java.sql.SQLException if an error appears when closing the Statement or Connection
     */
    public void disconnect() throws SQLException {
        if(connected) {
            statement.close();
            connection.close();
            connected = false;
            System.out.println("Database disconnected");
        }
    }

    /**
     * Sets the default directory where the database for this application will be created and maintained.
     * @param systemProperty the name of the system property e.g.: "derby.system.home"
     * @param subdirectory the name of the directory
     */
    public void setDBSystemDirectory(String systemProperty, String subdirectory) {
        String userHomeDirectory = System.getProperty("user.home", ".");
        String systemDirectory = userHomeDirectory + "/." + subdirectory;

        System.setProperty(systemProperty, systemDirectory);
    }
}
